<?php
date_default_timezone_set("Asia/Shanghai");
include 'functions.php';
include 'db.php';//数据库连接
$sql = "select * from ych_user where type_id=3 and fstatus=1";

$list = db_fetch_arrays($sql,$conn);

$start = strtotime('2014-07-01');
$end = strtotime('2014-08-01');

$result = array();

foreach($list as $val){
	$sql = "SELECT SUM(t.account) AS money FROM ych_borrow_tender t
		LEFT JOIN ych_borrow b ON t.borrow_id=b.id
		LEFT JOIN ych_user_vip v ON t.user_id=v.user_id 
		LEFT JOIN ych_user u ON t.user_id=u.user_id 
		WHERE b.fstatus IN(1,3) AND b.ftype<5 
		AND v.kefu_userid='{$val['user_id']}' 
		AND t.add_time>'{$start}' 
		AND t.add_time<'{$end}' 
		AND u.is_self=0";
		//AND t.user_id NOT IN(492,329,226,213,330,806,4724,4725,4726,5371,5372,5373,5374,5375)";
	
	$row = db_fetch_array($sql,$conn);
	$moneySum = intval($row['money']);
	
	$sql = "select sum(p1.account) as money from ych_yjb_tender as p1
		left join ych_user_vip as p2 on p1.user_id=p2.user_id
		where p1.add_time>{$start} and p1.add_time<{$end} 
		and p1.time_limit_day>=30 and p2.kefu_userid='{$val['user_id']}'";
	$row = db_fetch_array($sql,$conn);
	$yjbmoney = intval($row['money']);
	$moneySum = $moneySum + $yjbmoney;
	
	$sql = "select count(*) as num from ych_user_vip where kefu_userid='{$val['user_id']}' and vip_status=1";
	$row = db_fetch_array($sql,$conn);
	$userCount = $row['num'];
	
	$sql = "select count(*) as num from ych_user_vip where kefu_userid='{$val['user_id']}' and vip_status=1 and kefu_addtime>'{$start}' and kefu_addtime<'{$end}'";
	$row = db_fetch_array($sql,$conn);
	$newUserCount = $row['num'];
	
	$result[] = array(
		'user_id' => $val['user_id'],
		'username' => $val['username'],
		'money' => $moneySum,
		'user_count' => $userCount,
		'new_user_count' => $newUserCount
	);
}

?>
<h1><?php echo date('Y-m',$start);?>月客服客户投资统计</h1>
<table border="1" style="width: 600px;">
<tr>
<td>客服ID</td>
<td>客服</td>
<td>客户总数</td>
<td>该月新增客户数</td>
<td>该月客户投资总计</td>
</tr>
<?php foreach($result as $v){?>
<tr>
<td><?php echo $v['user_id'];?></td>
<td><?php echo $v['username'];?></td>
<td><?php echo $v['user_count'];?></td>
<td><?php echo $v['new_user_count'];?></td>
<td><?php echo $v['money'];?></td>
</tr>
<?php }?>
</table>